/*
 * Copyright (C), 2002-2017, 你的兔宝宝电子商务有限公司
 * FileName: Excels.java
 * Author:   你的兔宝宝
 * Date:     2017-6-16 上午10:53:43
 * Description: excel处理工具类//模块目的、功能描述      
 * History: //修改记录
 * <author>      <time>      <version>    <desc>
 * 修改人姓名             修改时间            版本号                  描述
 */
package com.baby.demo.utils;

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.dom4j.DocumentException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.ui.freemarker.FreeMarkerTemplateUtils;
import org.springframework.util.Assert;

import com.baby.demo.utils.factory.Json2mapFactoryBean;
import com.baby.demo.utils.factory.StaticsClassFactoryBean;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import freemarker.template.TemplateHashModel;

/**
 * excel处理工具类<br> 
 * 〈功能详细描述〉
 *
 * @author 你的兔宝宝
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
public class Xls {
    
    private static final Logger LOGGER = LoggerFactory.getLogger(Xls.class);

    private static String path2xlsconfig = "classpath:/conf/xls/xls-confing.xml.ftl";
    
    private static ResourceLoader resourceLoader = new DefaultResourceLoader();
    
    private final static Configuration CONF = new Configuration();
    
    static Map<String, Object> variables;
    
    static TemplateHashModel excels = null;
    
    static String path2conf = "classpath:/conf/xls/excels-config.json";
    static Map<String,Object> excel_conf = new HashMap<String,Object>();
    
    static int colorIndex = 0;
    
    static{
        CONF.setClassicCompatible(true);
        StaticsClassFactoryBean staticBean = new StaticsClassFactoryBean();
        staticBean.setStaticsClassName(Xls.class.getCanonicalName());
        try {
            staticBean.afterPropertiesSet();
            excels = staticBean.getObject();
        } catch (Exception e) {
            LOGGER.error("初始化环境失败",e);
        }
        
        Json2mapFactoryBean json2mapFactoryBean = new Json2mapFactoryBean();
        json2mapFactoryBean.setLocation(resourceLoader.getResource(Strings.show(path2conf).trim()));
        try {
            json2mapFactoryBean.afterPropertiesSet();
            excel_conf = json2mapFactoryBean.getObject();
        } catch (Exception e) {
            LOGGER.error("初始化环境失败",e);
        }
    }
    
    private Xls() {    }
    
    /**
     * 
     * 功能描述: map转excel<br>
     * 〈功能详细描述〉
     *
     * @param map   数据
     * @param path2xlsconfig    xls生成配置模板
     * @param out 输出
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static void map2xls(Map<String,Object> map,String path2xlsconfig,OutputStream out){
        // map->xls-xml->xls-map->xls
        try {
            // map2xml
            String excelXml = map2excelXml(map,path2xlsconfig);
            // xml2map
            Map<String,Object> excelMap = Xmls.xml2map(excelXml);
            //  map2xls
            excelMap2excel(excelMap,out);
        } catch (IOException e) {
            LOGGER.error("读取xls出错[读取异常]["+path2xlsconfig+"]",e);
        } catch (TemplateException e) {
            LOGGER.error("读取path2xlsconfig出错["+path2xlsconfig+"]",e);
        } catch (DocumentException e) {
            LOGGER.error("处理excelXml->excelMap异常["+path2xlsconfig+"]",e);
        }
    }
    
    /**
     * 
     * 功能描述: 设置excel样式<br>
     * 〈功能详细描述〉
     *
     * @param excelMap
     * @param workbook
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @SuppressWarnings("unchecked")
    private static Map<String,HSSFCellStyle> styles2excel(Map<String,Object> excelMap,HSSFWorkbook workbook){
        Map<String,HSSFCellStyle> styles_map = new HashMap<String,HSSFCellStyle>();
        //Styles
            //Style[]
                //Name,ID,Alignment,NumberFormat,Borders,Interior,Font
        List<Map<String,Object>> styles = Collections.path2list(excelMap, "Styles/Style");
        if( styles != null ){
            for( Map<String,Object> style : styles ){
                String styleId = Strings.get(style, "ID");
                HSSFCellStyle hssfstyle = workbook.createCellStyle();
                // dosomething
                style2excel(style,hssfstyle,workbook);
                styles_map.put(styleId, hssfstyle);
            }
        }
        return styles_map;
    }
    
    /**
     * 
     * 功能描述: 设置样式<br>
     * 〈功能详细描述〉
     *
     * @param style
     * @param hssfstyle
     * @param workbook
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @SuppressWarnings("unchecked")
    private static void style2excel(Map<String,Object> style,HSSFCellStyle hssfstyle,HSSFWorkbook workbook){
        //Name,ID,Alignment,NumberFormat,Borders,Interior,Font
        for(Map.Entry<String,Object> styleEntry:style.entrySet()){
            String styleName = styleEntry.getKey();
            Object styleObj = styleEntry.getValue();
            if( Strings.isNotBlank(styleName) && styleObj != null ){
                if( "Font".equals(styleName) && styleObj instanceof Map  ){//字体设置
                    HSSFFont font = workbook.createFont();
                    color2data((Map<String,Object>)styleObj,workbook);
                    set2obj((Map<String,Object>)styleObj,font);
                    hssfstyle.setFont(font);
                } else if( "Alignment".equals(styleName) && styleObj instanceof Map  ){//对齐设置
                    //Horizontal-Vertical
                    Map<String,Object> alignmentMap = (Map<String,Object>) styleObj;
                    alignment2style(alignmentMap,hssfstyle);
                } else if( "Borders".equals(styleName) && styleObj instanceof Map ){//边框设置
                    List<Map<String,Object>> borders = Collections.path2list((Map<String,Object>)styleObj, "Border");
                    borders2style(borders,hssfstyle,workbook);
                } else if( "Interior".equals(styleName) && styleObj instanceof Map ){//边框设置
                    Map<String,Object> interior = (Map<String, Object>) styleObj;
                    color2data( interior,workbook );
                    Collections.rename1(interior, "{Color:'InteriorColor'}");
                    set2obj( interior,hssfstyle );
                    //hssfstyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); 
                    hssfstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                }
                //Interior填充设置
            }
        }
    }
    
    /**
     * 
     * 功能描述: 边框样式设定<br>
     * 〈功能详细描述〉
     *
     * @param borders
     * @param hssfstyle
     * @param workbook
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @SuppressWarnings("unchecked")
    private static void borders2style(List<Map<String,Object>> borders,HSSFCellStyle hssfstyle,HSSFWorkbook workbook){
        Map<String,Object> bordersMap = new HashMap<String,Object>();
        Map<String,Integer> lineStyle = (Map<String,Integer>) excel_conf.get("LineStyle");
        for( Map<String,Object> border : borders ){
            color2data(border,workbook);
            String position = Strings.get(border, "Position");
            for(Map.Entry<String,Object> borderEntry:border.entrySet()){
                String borderName = borderEntry.getKey();
                Object borderValue = borderEntry.getValue();
                if( "LineStyle".equals(borderName) ){
                    if(lineStyle.containsKey(borderValue)){
                        borderValue = (short)lineStyle.get(borderValue).intValue();
                    }else{
                        continue;
                    }
                }
                bordersMap.put( position + borderName, borderValue );
            }
        }
        set2obj(bordersMap,hssfstyle);
    }
    
    /**
     * 
     * 功能描述: 处理颜色数据<br>
     * 〈功能详细描述〉
     *
     * @param data
     * @param workbook
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private static void color2data(Map<String,Object> data,HSSFWorkbook workbook){
        String color = Strings.get(data, "Color");
        if( Strings.isNotBlank(color) ){
            //处理把它转换成十六进制并放入一个数 
            int[] colorvalues = new int[3]; 
            colorvalues[0]=Integer.parseInt(color.substring(1, 3), 16); 
            colorvalues[1]=Integer.parseInt(color.substring(3, 5), 16); 
            colorvalues[2]=Integer.parseInt(color.substring(5, 7), 16); 
            HSSFPalette customPalette = workbook.getCustomPalette();  
            //index的取值范围 0x8 - 0x40
            short color_index = (short)((colorIndex ++ % 32) + 8) ;
            customPalette.setColorAtIndex( color_index , (byte) colorvalues[0], (byte)colorvalues[1], (byte)colorvalues[2]);
            LOGGER.debug("color_index:[{}]",new Object[]{color_index});
            data.put("Color", color_index );
        }
    }
    
    /**
     * 
     * 功能描述: 对齐方式设置<br>
     * 〈功能详细描述〉
     *
     * @param alignmentMap
     * @param hssfstyle
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @SuppressWarnings("unchecked")
    private static void alignment2style(Map<String,Object> alignmentMap,HSSFCellStyle hssfstyle){
        Map<String,Map<String,Integer>> alignment = (Map<String, Map<String, Integer>>) excel_conf.get("Alignment");
        for(Map.Entry<String,Map<String,Integer>> alignmentEntry:alignment.entrySet()){
            String alignmentName = alignmentEntry.getKey();
            Map<String,Integer> alignmentValues = alignmentEntry.getValue();
            if( alignmentMap.containsKey(alignmentName) ){
                String alignmentValue = Strings.get(alignmentMap, alignmentName);
                int alignmentInt = alignmentValues.get(alignmentValue);
                alignmentMap.put(alignmentName, (short)alignmentInt);
            }
        }
        set2obj(alignmentMap,hssfstyle);
    }
    
    /**
     * 
     * 功能描述: 设置定数据到指定对象中去<br>
     * 〈功能详细描述〉
     *
     * @param obj
     * @param data
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @SuppressWarnings("unchecked")
    private static void set2obj(Map<String,Object> data,Object obj){
        BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(obj);
        String objClassName = obj.getClass().getCanonicalName();
        Map<String,String> prop_maps = (Map<String, String>) excel_conf.get(objClassName);
        if( prop_maps != null && obj != null && data != null ){
            for(Map.Entry<String, String> propEntry : prop_maps.entrySet() ){
                String propname = propEntry.getKey();
                String method_propname = propEntry.getValue();
                if( !data.containsKey(propname) ){
                    continue;
                }
                PropertyDescriptor pd = BeanUtils.getPropertyDescriptor(obj.getClass(), method_propname);
                Object value = getMapValue(data,propname,pd.getPropertyType());
                if( value != null ){
                    bw.setPropertyValue(pd.getName(), value);
                }
            }
        }
    }
    
    /**
     * 
     * 功能描述: 获取map中并转换指定类型的值<br>
     * 〈功能详细描述〉
     *
     * @param data
     * @param propname
     * @param requiredType
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private static Object getMapValue(Map<String,Object> data,String propname,Class<?> requiredType){
        Object value = data.get(propname);
        if( value == null ){
            return value;
        }
        String svalue = Strings.show(value);
        Double dvalue = null;
        if( short.class.equals(requiredType) || Short.class.equals(requiredType) || int.class.equals(requiredType) || Integer.class.equals(requiredType) ){
            dvalue = Double.valueOf(svalue).doubleValue();
        }
        if (String.class.equals(requiredType)) {
            value = svalue;
        }
        else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) {
            value = Strings.getBoolean(value, false);
        }
        else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) {
            value = Byte.valueOf(svalue);
        }
        else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) {
            if( dvalue != null ){
                value = (short)dvalue.doubleValue();
            }else{
                value = null;
            }
        }
        else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) {
            if( dvalue != null ){
                value = (int)dvalue.doubleValue();
            }else{
                value = null;
            }
        }
        else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) {
            value = Long.valueOf(svalue);
        }
        else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) {
            value = Float.valueOf(svalue);
        }
        else if (double.class.equals(requiredType) || Double.class.equals(requiredType) ||
                Number.class.equals(requiredType)) {
            value = Double.valueOf(svalue);
        }
        return value;
    }
    
    /**
     * 
     * 功能描述: 设置样式到数据中<br>
     * 〈功能详细描述〉
     *
     * @param styles_map
     * @param data
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private static HSSFCellStyle style2data(Map<String,HSSFCellStyle> styles_map,Map<String,Object> data){
        String styleId = Strings.get(data, "StyleID");
        HSSFCellStyle hssfstyle = null;
        if( Strings.isNotBlank(styleId) && styles_map.containsKey(styleId) ){
            hssfstyle = styles_map.get(styleId);
        }
        data.put("StyleID",hssfstyle);
        return hssfstyle;
    }
    
    /**
     * 
     * 功能描述: 标准excel-map数据生产excel<br>
     * 〈功能详细描述〉
     *
     * @param excelMap
     * @param out
     * @throws IOException
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @SuppressWarnings("unchecked")
    public static void excelMap2excel(Map<String,Object> excelMap,OutputStream out) throws IOException{
        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
        
        // Styles 处理样式
        Map<String,HSSFCellStyle> styles_map = styles2excel(excelMap,workbook);
        // 处理表格
        //Worksheet[]
            //Nanme
            //Table
                //Row[]
                    //Index
                    //Cell[]
                        //StyleID
                        //Data{text,Type}
        List<Map<String,Object>> worksheets = Collections.path2list(excelMap, "Worksheet");
        Assert.notNull(worksheets, "Worksheet can't be null");
        for( Map<String,Object> worksheet:worksheets ){
            int row_num = 0;
            String sheetName = Strings.get(worksheet, "Name");
            HSSFSheet sheet = workbook.createSheet(sheetName); // 创建工作表
            //处理表格数据
            Map<String,Object> table = (Map<String, Object>) Collections.get(worksheet, "Table");
            int[] col_widths = null;
            // 处理行数据
            List<Map<String,Object>> rows = Collections.path2list(table, "Row");
            for( Map<String,Object> row:rows ){
                row_num = Strings.getInt(row, "Index", row_num);
                HSSFRow hssfrow = sheet.createRow(row_num);// 创建所需的行数
                // dosomething
                List<Map<String,Object>> cells = Collections.path2list(row, "Cell");
                
                //列宽数据处理
                if( col_widths == null ){
                    col_widths = new int[cells.size()];
                    Arrays.fill(col_widths, 0);
                }else if( col_widths.length < cells.size() ){
                    int[] temp_col_widths = col_widths;
                    col_widths = new int[cells.size()];
                    Arrays.fill(col_widths, 0);
                    com.baby.demo.utils.Arrays.copy(temp_col_widths, col_widths);
                }
                //处理列数据
                int cell_num = 0;
                for( Map<String,Object> cell:cells ){
                    cell_num = Strings.getInt(cell, "Index", cell_num);
                    Map<String,Object> data = (Map<String, Object>) Collections.get(cell, "Data");
                    if( !Collections.isEmpty(data) ){
                        HSSFCell  hssfcell = hssfrow.createCell(cell_num);
                        // dosomething
                        style2data(styles_map,cell);
                        String value = Strings.get(data, "text");
                        hssfcell.setCellValue(value);
                        set2obj(cell, hssfcell);
                        col_widths[cell_num] = Math.max(col_widths[cell_num], charLength(value));
                    }
                    cell_num ++;
                }
                row_num ++;
            }
            // 处理表格样式
            style2data(styles_map,table);
            Double d_sheet_width = (Double) getMapValue(table, "DefaultColumnWidth", Double.class);
            if( d_sheet_width!= null ){
                table.put("DefaultColumnWidth", ((int)d_sheet_width.doubleValue()/12)*256);
                //sheet.setDefaultColumnWidth(((int)d_sheet_width.doubleValue()/12)*256);
            }
            set2obj(table, sheet);
            
            // 处理列样式
            List<Map<String,Object>> columns = Collections.path2list(table, "Column");
            int col_index = 0;
            for( Map<String,Object> column:columns ){
                col_index = Strings.getInt(column, "Index", col_index);
                HSSFCellStyle hssfstyle = style2data(styles_map,column);
                //sheet.autoSizeColumn(col_index);
                if( hssfstyle != null ){
                    sheet.setDefaultColumnStyle(col_index, hssfstyle);
                }
                Double d_col_width = (Double) getMapValue(column, "Width", Double.class);
                if( d_col_width!= null ){
                    sheet.setColumnWidth(col_index, ((int)d_col_width.doubleValue()/12)*256);
                }
                col_index ++;
            }
            // 自动列宽
            for( int i = 0; i < col_widths.length; i ++ ){
                int col_width = col_widths[i];
                if( col_width > 0 ){
                    sheet.setColumnWidth(i, ( col_width ) * 256 );
                }
            }
        }
        workbook.write(out);
    }
    
    /**
     * 
     * 功能描述: 计算字符串长度<br>
     * 〈功能详细描述〉
     *
     * @param value
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private static int charLength(String value){
        return value.replaceAll("[^\\x00-\\xff]", "01").length();
    }
    
    /**
     * 
     * 功能描述: map转成标准excel-xml<br>
     * 〈功能详细描述〉
     *
     * @param map 数据
     * @param path2xlsconfig 生产excel-xml配置模板
     * @return
     * @throws IOException
     * @throws TemplateException
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static String map2excelXml(Map<String,Object> map,String path2xlsconfig) throws IOException, TemplateException{
        Resource  resource = resourceLoader.getResource(Strings.show(path2xlsconfig).trim());
        Template tpl = new Template(path2xlsconfig, new InputStreamReader(resource.getInputStream()), CONF);
        String excelXml = FreeMarkerTemplateUtils.processTemplateIntoString(tpl, map);
        LOGGER.debug( "excelXml:" + excelXml);
        return excelXml;
    }
    
    /**
     * 
     * 功能描述: excel转xml<br>
     * 〈功能详细描述〉
     *
     * @param path2xls
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static String xls2xml(String path2xls){
        try {
            return xls2xml(path2xls,path2xlsconfig);
        } catch (IOException e) {
            LOGGER.error("读取xls出错[读取异常]["+path2xls+"]",e);
        } catch (InvalidFormatException e) {
            LOGGER.error("读取xls出错[xls格式不对]["+path2xls+"]",e);
        } catch (TemplateException e) {
            LOGGER.error("读取path2xlsconfig出错["+path2xlsconfig+"]",e);
        } catch (Exception e) {
            LOGGER.error("读取xls出错[处理异常]",e);
        }
        return Strings.EMPTY;
    }
    
    public static String xls2xml(String path2xls,String path2xlsconfig) throws InvalidFormatException,IOException, TemplateException{
        Resource  resource = resourceLoader.getResource(Strings.show(path2xls).trim());
        String uri = resource.getURI().toString();
        LOGGER.debug( "resource.getURI()" + uri);
        return xls2xml(resource.getInputStream(),path2xlsconfig);
    }
    
    public static String xls2xml(InputStream fis,String path2xlsconfig) throws InvalidFormatException,IOException, TemplateException{
        Workbook wb = WorkbookFactory.create(fis);
        Resource  resource = resourceLoader.getResource(Strings.show(path2xlsconfig).trim());
        Map<String,Object> params = Collections.noop();
        Template tpl = new Template(path2xlsconfig, new InputStreamReader(resource.getInputStream()), CONF);
        params.put("wb", wb);
        params.put("excels", excels);
        String xml = FreeMarkerTemplateUtils.processTemplateIntoString(tpl, params);
        LOGGER.debug( "xls2xml:" + xml);
        return xml;
    }
    
    /**
     * 
     * 功能描述: 获取cell数据<br>
     * 〈功能详细描述〉
     *
     * @param cell
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static String getValue(Cell cell) {
        String value = Strings.EMPTY;
        if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            value = String.valueOf(cell.getBooleanCellValue());
        }else if( cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值 
            double num = cell.getNumericCellValue();
            value = String.valueOf(num);
            boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
            isDate = isDate && HSSFDateUtil.isCellInternalDateFormatted(cell);
            if(isDate && HSSFDateUtil.isValidExcelDate(num)){
                value = Strings.format(HSSFDateUtil.getJavaDate(num), "yyyy-MM-dd hh:mm:ss");
            }
        }else{
            value = String.valueOf(cell.getStringCellValue());
        }
        return value;
    }
    
    /**
     * 
     * 功能描述: 获取cell字符数据<br>
     * 〈功能详细描述〉
     *
     * @param cell
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static String getStringValue(Cell cell) {
        String value = Strings.EMPTY;
        if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            value = String.valueOf(cell.getBooleanCellValue());
        }else if( cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值 
            double num = cell.getNumericCellValue();
            boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
            isDate = isDate && HSSFDateUtil.isCellInternalDateFormatted(cell);
            if(isDate && HSSFDateUtil.isValidExcelDate(num)){
                value = Strings.format(HSSFDateUtil.getJavaDate(num), "yyyy-MM-dd hh:mm:ss");
            }else
                value = Strings.parseNum(String.valueOf(num));//getRichStringCellValue
        }else{
            value = String.valueOf(cell.getStringCellValue());
        }
        return value;
    }
    
    /**
     * 
     * 功能描述: 获取cell数据类型<br>
     * 〈功能详细描述〉
     *
     * @param cell
     * @return
     * @Author:   你的兔宝宝
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static String getType(Cell cell) {
        String type = "String";
        if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            type = "Boolean";
        }else if( cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值 
            double num = cell.getNumericCellValue();
            boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
            isDate = isDate && HSSFDateUtil.isCellInternalDateFormatted(cell);
            if(isDate && HSSFDateUtil.isValidExcelDate(num)){
                type = "String";
            }else{
                type = "Number";
            }
        }
        return type;
    }
    
}
